import time
from flask import Blueprint, render_template, request, redirect
import sqlite3
import os

book_blueprint = Blueprint('book', __name__)
DATABASE = 'home.db'

def get_db_connection():
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    return conn

def execute_sql_result(sql, *args):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute(sql, args)
    result = cursor.fetchall()
    cursor.close()
    conn.close()
    return result

def execute_sql_no_result(sql, *args):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute(sql, args)
    conn.commit()
    cursor.close()
    conn.close()

@book_blueprint.route('/addbook')
def add_book():
    return render_template('add_book.html')

@book_blueprint.route('/savebook', methods=['POST'])
def save_book():
    files = request.files.getlist('files')
    urls = []
    upload_folder = os.path.join('static', 'upload')
    if not os.path.exists(upload_folder):
        os.makedirs(upload_folder)

    for file in files:
        if file and file.filename:
            ext = file.filename.rsplit('.', 1)[-1]
            fname = str(int(round(time.time() * 1000))) + '.' + ext
            file.save(os.path.join(upload_folder, fname))
            urls.append(fname)

    # 补足5个图片url字段，防止索引越界
    while len(urls) < 5:
        urls.append('')

    book_name = request.form.get('book_name')
    author_name = request.form.get('author_name')
    publisher_name = request.form.get('publisher_name')
    price = request.form.get('price')
    page_number = request.form.get('page_number')
    isbn_number = request.form.get('isbn_number')
    publish_date = request.form.get('publish_date')
    uploader_name = request.form.get('uploader_name')

    sql = '''
    INSERT INTO book (book_name, author_name, publisher_name, price, page_number, isbn_number, publish_date,
                      img_url, img1_url, img2_url, img3_url, img4_url, uploader_name)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    '''
    execute_sql_no_result(sql,
                          book_name, author_name, publisher_name, price, page_number, isbn_number,
                          publish_date, urls[0], urls[1], urls[2], urls[3], urls[4], uploader_name)

    sql = 'SELECT * FROM book WHERE uploader_name=?'
    result = execute_sql_result(sql, uploader_name or 'wupicheng')
    return render_template('book_list.html', books=result)

@book_blueprint.route('/lists')
def book_list():
    sql = 'SELECT * FROM book WHERE uploader_name=?'
    result = execute_sql_result(sql, 'wupicheng')
    return render_template('book_list.html', books=result)

@book_blueprint.route('/delete/<int:book_id>')
def book_delete(book_id):
    sql = 'DELETE FROM book WHERE book_id=?'
    execute_sql_no_result(sql, book_id)
    return redirect('/book/lists')

@book_blueprint.route('/detail/<int:book_id>')
def book_detail(book_id):
    sql = 'SELECT * FROM book WHERE book_id=?'
    result = execute_sql_result(sql, book_id)
    if result:
        return render_template('book_detail.html', book=result[0])
    else:
        return "书籍未找到", 404
